import os
from collections import Counter
from datetime import datetime
from itertools import groupby

import pandas as pd
import xlsxwriter
from django.db.models import Q, Count
from django.utils.timezone import make_aware
from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
from rest_framework import viewsets, status
from django.http import JsonResponse, HttpResponse
from rest_framework.response import Response
from rest_framework.views import APIView
from xlsxwriter import worksheet, workbook

from conf.common_conf import res200, res400
from conf.http_conf import REQUEST_GET_SUCCESS
from course.models import TrainCourseExpense, TrainCourse, TrainCourseTrainee
from course.serializers import TrainCourseExpenseSerializer
from middleware.authentication import MyAuthentication
from tools.db_conn.conn_info import db_select_info
from tools.db_conn.conn_info import db_select_info
from tools.page_tools.page_info import paginate
from user.models import Trainee


class DetailedExpenseListView(viewsets.ModelViewSet):
    authentication_classes = [MyAuthentication, ]

    #时间条件查询   获取培训中心举办培训班应收各单位培训费明细单  excel格式   多文件导出
    def list(self,request,*args, **kwargs):
        try:
            #时间查询，培训班表关联培训班费用表，输出汇总表（包含培训班名称，时间，人数，金额(培训班单人单价)：培训班费用表TrainCourseExpense+
            # 教师费用TrainCourseTeacherExpense
            # （根据课程id统计所有老师课酬总费用），培训班总费用）

            #谢乐 时间查询，培训班表关联培训班费用表，输出汇总数据

            #数据分类--->总表，按公司分表

            return HttpResponse(res200(REQUEST_GET_SUCCESS, ''))
        except Exception as e:
            print(str(e))
            return HttpResponse(res400(str(e)))



class TrainCourseListView(viewsets.ModelViewSet):
    authentication_classes = [MyAuthentication, ]

    def list(self, request, *args, **kwargs):
        try:

            course_start_time = request.data.get('course_start_time')
            course_end_time = request.data.get('course_end_time')
            select_sql = (
            f"SELECT unit_name, name, branch, post, b.course_name "
            f"FROM train_course_trainee a "
            f"LEFT JOIN (SELECT id, course_name FROM train_course "
            f"WHERE course_start_time >= '{course_start_time}' & "
            f"course_end_time <= '{course_end_time}') b ON a.train_course_id = b.id "
            )

            info_tuples = db_select_info(select_sql)
            columns = ['unit_name', 'name', 'branch', 'post', 'course_name']
            df = pd.DataFrame(info_tuples, columns=columns)
            df.rename(columns={
            'name': '姓名',
            'unit_name': '所在单位',
            'branch': '部门',
            'post': '职称',
            }, inplace=True)
            file_path='./train_course_excel/train_course_excel.xlsx'
            workbook = xlsxwriter.Workbook(file_path)
            format_bold_center = workbook.add_format({'bold': True, 'align': 'center', 'valign': 'vcenter'})
            format_center = workbook.add_format({'align': 'center'})
            table_header_format = workbook.add_format({'bold': True, 'bg_color': '#D3D3D3', 'align': 'center'})

            for unit_name, group in df.groupby('所在单位'):
                sheet_name = unit_name
                counter = 1
                while workbook.get_worksheet_by_name(sheet_name):
                    sheet_name = f"{unit_name} ({counter})"
                    counter += 1

                worksheet = workbook.add_worksheet(sheet_name)
                row_start = 0

                for course_name, entries in group.groupby('course_name'):
                    #df_course = entries.reset_index().drop('index', axis=1)
                    df_course = entries.drop('course_name', axis=1).reset_index().drop('index', axis=1)
                    df_course.insert(0, '序号', range(1, len(df_course) + 1))
                    df_course['总人数'] = 1  # 明确设置总人数为1，便于后续计算

                    # 保持列名不变，但是实际写入的是总人数
                    columns = df_course.columns.tolist()
                    columns[-1] = '总人数'

                    worksheet.merge_range(row_start, 0, row_start, len(columns) - 1, course_name, format_bold_center)
                    worksheet.write_row(row_start + 1, 0, columns, table_header_format)
                    fixed_width = 20
                    for col_num, _ in enumerate(columns):
                        worksheet.set_column(col_num, col_num, fixed_width)
                    # 写入数据行
                    for i, row in df_course.iterrows():
                        worksheet.write_row(row_start + i + 2, 0, row.values[:-1], format_center)
                        worksheet.write(row_start + i + 2, len(columns) - 1, row['总人数'], format_center)
                    # 计算总人数
                    total_count = df_course['总人数'].sum()
                    # 设置列宽
                    # 合并单元格并写入总人数
                    worksheet.merge_range(row_start + 2, len(columns) - 1,
                                      row_start + len(df_course) + 1, len(columns) - 1,
                                      total_count, format_bold_center)
                    row_start += len(df_course) + 4

            workbook.close()

            print(
                "Excel文件已生成，包含多个以部门命名的sheet页，其中每个课程的数据作为一个表格，并在每个课程名称下方显示该课程的总人数。")
            return JsonResponse(res200("Excel文件已生成或已更新。",file_path))
        except PermissionError as e:
            # 处理权限错误
            return JsonResponse(res400("无法写入文件：权限被拒绝,请检查下载文件是否已被打开。"))
        except Exception as e:
            # 处理其他异常
            return JsonResponse(res400(str(e)))


class TrainCourseExpenseView(viewsets.ModelViewSet):

    serializer_class = TrainCourseExpenseSerializer
    authentication_classes = [MyAuthentication, ]

    def export(self, request, *args, **kwargs):
        try:
            sponsor = self.request.query_params.get('sponsor','')
            course_start_time = self.request.query_params.get('course_start_time', '')
            course_end_time = self.request.query_params.get('course_end_time', '')
            # print(sponsor)
            select_sql = (
                f"SELECT t.sponsor,course_name,combined_time,person_count,person_cost,train_course_total_cost FROM (SELECT tc.sponsor,tc.course_name,tc.course_start_time || ' ~ ' || tc.course_end_time AS combined_time,tc.person_count,round(train_course_total_cost/person_count,2) as person_cost,train_course_total_cost FROM train_course tc LEFT JOIN train_course_expense tce on tce.train_course_id = tc.id) as t group BY t.sponsor,course_name,combined_time,person_count,person_cost,train_course_total_cost ")
            a =(f"SELECT tc.sponsor,tc.course_name,tc.course_start_time || ' - ' || tc.course_end_time AS combined_time,tc.person_count,round( train_course_total_cost / person_count, 2 ) AS person_cost,train_course_total_cost FROM train_course tc LEFT JOIN train_course_expense tce ON tce.train_course_id = tc.id "
                f"where 1 = 1 ")
            if course_start_time != '' :
                a += f" and tc.course_start_time  >= '{course_start_time}'"
            if course_end_time != '' :
                a += f" and tc.course_end_time  <= '{course_end_time}'"
            query_data = db_select_info(a)
            # print(query_data)

            # 解析JSON数据并确保它是一个列表
            if not isinstance(query_data, list):
                raise ValueError("Expected a list in the request body.")
            data_list =[]
            for row in query_data :
                data_list.append({'sponsor':row[0],'course_name':row[1],'combined_time':row[2],'people_count':row[3],'person_cost':row[4],'train_course_total_cost':row[5]})
            # print(data_list)

            # 按照"age"字段对列表进行排序
            sorted_data = sorted(data_list, key=lambda x: x["sponsor"])

            # 使用groupby()函数按照"age"字段进行分组
            grouped_data = {k: list(v) for k, v in groupby(sorted_data, key=lambda x: x["sponsor"])}

            print(grouped_data)

            # 构造Excel响应
            response = HttpResponse(content_type='application/json.openxmlformats-officedocument.spreadsheetml.sheet')
            response[
                'Content-Disposition'] = 'attachment; filename="D:\\learnproject\\djangoLearn\\training-management-system"'

            # # 创建一个默认字典，key为单位名，value为属于该单位的所有记录的列表
            # grouped_data = defaultdict(list)

            # 遍历数据，按单位进行分组
            # for item in data_list:
            #     grouped_data[item['sponsor']].append(item)

                # 创建一个新的Excel文件并添加一个工作表
            # 获取当前时间并格式化为字符串
            current_time = datetime.now().strftime('%Y-%m-%d_%H-%M-%S')
            file_path = f"fee/fee_{current_time}.xlsx"

            # 使用格式化后的时间字符串作为文件名
            workbook = xlsxwriter.Workbook(file_path)
            worksheet = workbook.add_worksheet('各单位应交费用')
            # 设置第一行的高度为20像素
            worksheet.set_row(0, 40)

            title_format = {
                'bold': True,  # 加粗
                'align': 'center',  # 水平位置设置：居中
                'valign': 'vcenter', # 垂直位置设置，居中
                # 'border': 1
            }
            title_format = workbook.add_format(title_format)  # 设置要调整的格式，可参阅链接文章实现更多样式选择
            #
            # 定义标题行数据
            title_row=["序号", "单位", "班名", "培训时间", "人数", "金额", "培训费用（元)","联系人"]
            # 写入标题行
            worksheet.write_row('A1',title_row)            # 应用格式到标题行的所有单元格
            for col in range (len(title_row)):
                if col == 0:
                    worksheet.set_column(col, col, None, title_format)
                if col == 1:
                    worksheet.set_column(col, col, 20, title_format)
                if col == 2:
                    worksheet.set_column(col, col, 50, title_format)
                if col == 3:
                    worksheet.set_column(col, col, 30, title_format)
                if col == 4:
                    worksheet.set_column(col, col, None, title_format)
                if col == 5:
                    worksheet.set_column(col, col, None, title_format)
                if col == 6:
                    worksheet.set_column(col, col, 20, title_format)
                if col == 7:
                    worksheet.set_column(col, col, 20, title_format)
            #
            content_format = {
                'align': 'center',  # 水平位置设置：居中
                'valign': 'vcenter',  # 垂直位置设置，居中
                # 'border': 1
            }
            content_format = workbook.add_format(content_format)
            # 打印分组结果
            row = 1
            # 合并序号
            k = 0
            # 总的行数
            total = 0
            # 各单位应交费用汇总
            for items in grouped_data.values():
                # print(f"sponsor: {sponsor}")
                # 上次行数
                up = total +1
                if row != 1:
                    row = total
                col = 1
                count = []
                sponsor = ''
                for item in items:
                    # print(item)
                    worksheet.write(row, col, item.get("sponsor"), content_format)
                    worksheet.write(row, col + 1, item.get("course_name"), content_format)
                    worksheet.write(row, col + 2, item.get("combined_time"), content_format)
                    worksheet.write(row, col + 3, item.get("people_count"), content_format)
                    worksheet.write(row, col + 4, item.get("person_cost"), content_format)
                    worksheet.write(row, col + 5, round(float(item.get("train_course_total_cost", 0)), 2), content_format)
                    count.append(round(float(item.get("train_course_total_cost", 0)), 2))
                    row += 1
                    sponsor = item.get("sponsor")
                k += 1
                final_count = sum(count)
                # print("final_count:", final_count)
                # print("row:", row)

                total = row + 1
                # print("sk:", k)

                worksheet.merge_range('C{}:F{}'.format(total, total), "合计")
                worksheet.write_row('G{}'.format(total), [final_count])
                if k==1:
                    worksheet.merge_range('A2:A{}'.format(total), k)
                    worksheet.merge_range('B2:B{}'.format(total), sponsor)
                    worksheet.merge_range('H2:H{}'.format(total), None)
                else:
                    worksheet.merge_range('A{}:A{}'.format(up,total), k)
                    worksheet.merge_range('B{}:B{}'.format(up,total), sponsor)
                    worksheet.merge_range('H{}:H{}'.format(up,total), None)

                print("\n")
            for items in grouped_data.values():
                worksheet = workbook.add_worksheet(items[0].get("sponsor"))
                worksheet.set_row(0, 40)
                # 定义标题行数据
                title_row = ["序号",  "班名", "培训时间", "人数", "金额", "培训费用（元)", "联系人"]
                # 写入标题行
                worksheet.write_row('A1', title_row)  # 应用格式到标题行的所有单元格
                for col in range(len(title_row)):
                    if col == 0:
                        worksheet.set_column(col, col, None, title_format)
                    if col == 1:
                        worksheet.set_column(col, col, 50, title_format)
                    if col == 2:
                        worksheet.set_column(col, col, 30, title_format)
                    if col == 3:
                        worksheet.set_column(col, col, None, title_format)
                    if col == 4:
                        worksheet.set_column(col, col, None, title_format)
                    if col == 5:
                        worksheet.set_column(col, col, 20, title_format)
                    if col == 6:
                        worksheet.set_column(col, col, 20, title_format)
                row = 1
                col = 0
                count = []
                for item in items:
                    worksheet.write(row, col, row, content_format)
                    worksheet.write(row, col + 1, item.get("course_name"), content_format)
                    worksheet.write(row, col + 2, item.get("combined_time"), content_format)
                    worksheet.write(row, col + 3, item.get("people_count",0), content_format)
                    worksheet.write(row, col + 4, item.get("person_cost",0), content_format)
                    worksheet.write(row, col + 5, round(float(item.get("train_course_total_cost", 0)), 2),content_format)
                    count.append(round(float(item.get("train_course_total_cost", 0)), 2))
                    row += 1
                final_count = sum(count)
                total = row + 1
                worksheet.merge_range('B{}:E{}'.format(total, total), "合计")
                worksheet.write_row('F{}'.format(total), [final_count])
                worksheet.merge_range('G2:G{}'.format(total), None)
                print("\n")
            # 关闭Excel文件
            workbook.close()
            return JsonResponse(res200("培训班费用下载完成。", file_path))
        except Exception as e:
            # 处理其他异常
            return JsonResponse(res400(str(e)))

    def list(self, request, *args, **kwargs):
        try:
            sponsor = self.request.query_params.get('sponsor','')
            course_start_time = self.request.query_params.get('course_start_time', '')
            course_end_time = self.request.query_params.get('course_end_time', '')
            print(sponsor)
            select_sql = (
                f"SELECT t.sponsor,course_name,combined_time,person_count,person_cost,train_course_total_cost FROM (SELECT tc.sponsor,tc.course_name,tc.course_start_time || ' ~ ' || tc.course_end_time AS combined_time,tc.person_count,round(train_course_total_cost/person_count,2) as person_cost,train_course_total_cost FROM train_course tc LEFT JOIN train_course_expense tce on tce.train_course_id = tc.id) as t group BY t.sponsor,course_name,combined_time,person_count,person_cost,train_course_total_cost ")
            a =(f"SELECT tc.sponsor,tc.course_name,tc.course_start_time || ' - ' || tc.course_end_time AS combined_time,tc.person_count,round( train_course_total_cost / person_count, 2 ) AS person_cost,train_course_total_cost FROM train_course tc LEFT JOIN train_course_expense tce ON tce.train_course_id = tc.id "
                f"where tc.sponsor like '%{sponsor}%' ")
            if course_start_time != '' :
                a += f" and tc.course_start_time  >= '{course_start_time}'"
            if course_end_time != '' :
                a += f" and tc.course_end_time  <= '{course_end_time}'"
            query_data = db_select_info(a)
            # print(query_data)

            # 分页参数
            page_size_str = request.query_params.get('size', '10')
            page_size = int(page_size_str) if page_size_str.isdigit() else 10  # 默认为10

            page_number_str = request.query_params.get('page', '1')
            page_number = int(page_number_str) if page_number_str.isdigit() else 1  # 默认为1

            # 分页处理
            page_data, pagination_info = paginate(query_data, page_size, page_number)

            # 返回包含数据和分页信息的JSON响应
            response_data = {
                'data': page_data,
                'pagination': pagination_info,
            }
            return Response(res200(REQUEST_GET_SUCCESS, response_data))

        except ValueError as e:
            # 处理数据类型错误
            return Response({"msg": str(e)}, status=400)
        except Exception as e:
            # 处理其他异常
            return Response({"msg": str(e)}, status=500)

# class TraineeDataView(viewsets.ModelViewSet):
#
#     serializer_class = TrainCourseExpenseSerializer
#     authentication_classes = [MyAuthentication, ]
#     def list(self, request, *args, **kwargs):
#         try:
#             date = self.request.query_params.get('date','')
#             print(sponsor)
#             a =(f"SELECT tc.sponsor,tc.course_name,tc.course_start_time || ' - ' || tc.course_end_time AS combined_time,tc.person_count,round( train_course_total_cost / person_count, 2 ) AS person_cost,train_course_total_cost FROM train_course tc LEFT JOIN train_course_expense tce ON tce.train_course_id = tc.id "
#                 f"where tc.sponsor like '%{sponsor}%' ")
#             if course_start_time != '' :
#                 a += f" and tc.course_start_time  >= '{course_start_time}'"
#             if course_end_time != '' :
#                 a += f" and tc.course_end_time  <= '{course_end_time}'"
#             query_data = db_select_info(a)
#             # print(query_data)
#
#             # 分页参数
#             page_size_str = request.query_params.get('size', '10')
#             page_size = int(page_size_str) if page_size_str.isdigit() else 10  # 默认为10
#
#             page_number_str = request.query_params.get('page', '1')
#             page_number = int(page_number_str) if page_number_str.isdigit() else 1  # 默认为1
#
#             # 分页处理
#             page_data, pagination_info = paginate(query_data, page_size, page_number)
#
#             # 返回包含数据和分页信息的JSON响应
#             response_data = {
#                 'data': page_data,
#                 'pagination': pagination_info,
#             }
#             return Response(res200(REQUEST_GET_SUCCESS, response_data))
#
#         except ValueError as e:
#             # 处理数据类型错误
#             return Response({"error": str(e)}, status=400)
#         except Exception as e:
#             # 处理其他异常
#             return Response({"error": str(e)}, status=500)

class FeeByCompanyView(APIView):

    authentication_classes = [MyAuthentication, ]
    def get(self, request):
        try:
            date = request.query_params.get('yearmonth','')
            a = (f'SELECT SUM(round(people * money,2)) AS cost, unit_name FROM (SELECT count(1) '  
             f'AS people, train_course_id, unit_name FROM train_course_trainee WHERE '  
             f'train_course_id IN (SELECT id FROM train_course WHERE SUBSTR(course_start_time, 1, 7) = \'{date}\') GROUP BY '  
             f'unit_name,train_course_id) a LEFT JOIN (SELECT personal_train_expense AS money, train_course_id FROM train_course_expense WHERE '  
             f'train_course_id IN (SELECT id FROM train_course WHERE SUBSTR(course_start_time, 1, 7) = \'{date}\')) '  
             f'b ON a.train_course_id = b.train_course_id GROUP BY unit_name ')
            query_data = db_select_info(a)
            return JsonResponse({"code":200,"data":query_data})
        except ValueError as e:
            # 处理数据类型错误
            return Response({"msg": str(e)}, status=400)
        except Exception as e:
            # 处理其他异常
            return Response({"msg": str(e)}, status=500)

class TraineeCountByCompanyView(viewsets.ModelViewSet):
    authentication_classes = [MyAuthentication, ]

    def list(self, request, *args, **kwargs):
        try:
            course_time = self.request.query_params.get('yearmonth', '')
            a = (
                f"SELECT tc.sponsor, count(*) as count FROM  train_course_trainee AS tct  LEFT JOIN train_course AS tc  ON tct.train_course_id = tc.id "
                f"where 1=1 ")
            if course_time != '':
                a += f" and SUBSTR(tc.course_start_time, 1,7)  == '{course_time}'"
            a += f"GROUP BY sponsor"
            query_data = db_select_info(a)
            data_list = []
            for row in query_data:
                data_list.append({'sponsor': row[0], 'count': row[1]})
            return JsonResponse(res200(REQUEST_GET_SUCCESS, data_list))
        except Exception as e:
            # 处理其他异常
            return JsonResponse(res400(str(e)))
class TrainCourseExpenseCostView(viewsets.ModelViewSet):
    authentication_classes = [MyAuthentication, ]

    def list(self, request, *args, **kwargs):

        try:
            course_start_time = request.data.get('yearmonth')
            if not course_start_time:
                return JsonResponse(res400("缺少年份或月份参数。"))
            select_sql =(
                f"SELECT SUM(course_site_rent) AS course_site_rent, "
                f" SUM(course_meal_fee) AS course_meal_fee, SUM(management_cost) AS management_cost, "
                f"SUM(courseware_cost) AS courseware_cost, SUM(printing_fee) AS printing_fee, "
                f"SUM(car_fare) AS car_fare, SUM(quarterage) AS quarterage, "
                f"SUM(file_bag_fee) AS file_bag_fee, SUM(tea_break_fee) AS tea_break_fee, "
                f"SUM(other_fee) AS other_fee, SUM(teacher_expense) AS teacher_expense "
                f"FROM ("
                f"SELECT SUM(course_site_rent) AS course_site_rent, "
                f" SUM(course_meal_fee) AS course_meal_fee, SUM(management_cost) AS management_cost, "
                f"SUM(courseware_cost) AS courseware_cost, SUM(printing_fee) AS printing_fee, "
                f"SUM(car_fare) AS car_fare, SUM(quarterage) AS quarterage, "
                f"SUM(file_bag_fee) AS file_bag_fee, SUM(tea_break_fee) AS tea_break_fee, "
                f"SUM(other_fee) AS other_fee, SUM(teacher_expense) AS teacher_expense, "
                f"a.train_course_id as train_course_id"
                f" FROM train_course_expense a "
                f"LEFT JOIN train_course_teacher_expense b ON a.train_course_id = b.train_course_id "
                f"GROUP BY a.train_course_id "
                f") c "
                f"WHERE c.train_course_id IN (SELECT id FROM train_course WHERE SUBSTR(course_start_time, 1, 7) = '{course_start_time}')")
            info = db_select_info(select_sql)
            info_json = [
                {
                    'course_site_rent': item[0],
                    'course_meal_fee': item[1],
                    'management_cost': item[2],
                    'courseware_cost': item[3],
                    'printing_fee': item[4],
                    'car_fare': item[5],
                    'quarterage': item[6],
                    'file_bag_fee': item[7],
                    'tea_break_fee': item[8],
                    'other_fee': item[9],
                    'teacher_expense': item[10]
                }
                for item in info
            ]
            return JsonResponse(res200("查询成功!",info_json))
        except Exception as e:
            # 处理其他异常
            return JsonResponse(res400(str(e)))

class TraineeCountSelectView(viewsets.ModelViewSet):
    authentication_classes = [MyAuthentication, ]

    def list(self, request, *args, **kwargs):
        try:
            count = Trainee.objects.all().count()
            return JsonResponse(res200(REQUEST_GET_SUCCESS, {'count':count}))
        except Exception as e:
            # 处理其他异常
            return JsonResponse(res400(str(e)))


